Pyramid's Formulate toolset enables non-technical users to build advanced business logic calculations, lists, KPIs, parameters, and scripts using a point-and-click interface. The code editor extends this functionality with an integrated development environment ("IDE") for advanced users to write both PQL and MDX statements, complete with drag-and-drop function libraries and metadata models.
To review an example of how to build a custom member in the IDE, click here.
- Click here to read how the PQL Editor works.
Access the Script Editor
To access the IDE, click Script Mode from the top ribbon in the Custom Member designer.
Script Editor Workspace
- From the Script Editor, drag the function you want to use from the Functions listing (red arrow below) onto the canvas (orange highlight).
- From the Metadata panel (blue arrow below), inject the required model metadata into the function.
- Use the Style options in the ribbon (blue highlight) to set the design theme and text size. The design theme determines the background color of the script editor, and the text color.
- As usual, click Run (green arrow) to test the function; the results are displayed in the Result Preview panel (purple highlight).
Note: You can search for the function you want, and hover over each function to see a tooltip about it (green highlight).
When you add custom members or global parameters from the Metadata panel to the script editor, you can hover over the Item ID to see the item name, its folder location, who created it, the create date, modification date, item ID, and formula type.
You can also open the formula in its relevant editor by holding Ctrl and clicking on the Item ID.
Script Language
The script language is PQL, unless using OLAP, Tabular or BW models, in which case the script language is MDX. To review a reference library of MDX functions, see Microsoft's MDX Function Reference.
Use the ChatGPT AI-driven PQL function (from the Common PQL functions) to write PQL expressions and generate various assets. This can be used to add additional information and background to your presentation; it cannot tell you about your data set.
Use AI to Generate PQL
Use the Generative AI integration to generate PQL or MDX code. This is useful if you want to generate code quickly, or don't know the syntax, for instance. To do this, click AI Formula from the ribbon. In the text field at the top of the Generate Your PQL dialog (or Generate Your MDX dialog for MS OLAP, Tabular, and BW models), enter a description of the query you want to perform.
- Text Field: enter a description of the query you want to perform and click the arrow to enter your query description and return a it as a script. (red highlight below).
- Schemas: the schemas panel exposes the database schema (orange highlight below); you can select the dimensions, elements, and views that are relevant to your description to simplify the query,
- Script Window: the AI-generated script will appear in the scripting window (green highlight below).
- Apply: apply the script to the Query node.
- Cancel: close the dialog without applying any changes.
Warning: AI-generated assets are generated from public domain algorithms, which can produce both erroneous and inconsistent or random results. Use at your own risk.
Use AI to Build Custom Measures
Use the ChatGPT AI-driven PQL function to create custom measures, which can then be injected into queries. To do this, enter your text prompt and the relevant members or hierarchies into the ChatGPT function.
You can then inject the custom measure into a grid to display the AI-generated values.
Example. Use AI to Build Custom Measures
First a custom measure returning the approximate population of countries within the Country hierarchy was created:
A second custom measure was created to return a one-word description of each country in the Country hierarchy:
Next, the Country hierarchy and both custom measures were added to a grid, populating the grid with the values generated by the custom measures:
